/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Modele;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashSet;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author sio
 */
public class DAO {

    public static Collection<Dep> getLesDeps() {
        Collection<Dep> lesDeps = new HashSet<Dep>();
        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs = req.executeQuery("select distinct departement from medecin;");

            while (rs.next()) {
//            Dep unDep = new Dep (rs.getString("departement"), null);
//            lesDeps.add(unDep);
                Statement req2;
                req2 = con.createStatement();
                ResultSet rs2 = req2.executeQuery("select * from medecin where departement = '" + rs.getString("departement") + "';");
                Collection<Med> lesMeds = new HashSet<Med>();
                while (rs2.next()) {
                    Med unMed = new Med(rs2.getString("nom"), rs2.getString("prenom"), rs2.getString("adresse"), rs2.getString("tel"),
                            rs2.getString("specialitecomplementaire"), rs2.getString("id"));
                    lesMeds.add(unMed);
                }
                Dep unDep = new Dep(rs.getString("departement"), lesMeds);
                lesDeps.add(unDep);
            }
            return lesDeps;
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lesDeps;
    }

    public static Collection<Spe> getLesSpe() {
        Collection<Spe> lesSpe = new HashSet<Spe>();
        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs = req.executeQuery("select distinct specialitecomplementaire from medecin;");

            while (rs.next()) {

                Statement req2;
                req2 = con.createStatement();
                ResultSet rs2 = req2.executeQuery("select * from medecin where specialitecomplementaire = '" + rs.getString("specialitecomplementaire") + "';");
                Collection<Med> lesMeds = new HashSet<Med>();
                while (rs2.next()) {
                    Med unMed = new Med(rs2.getString("nom"), rs2.getString("prenom"), rs2.getString("adresse"), rs2.getString("tel"),
                            rs2.getString("specialitecomplementaire"), rs2.getString("id"));
                    lesMeds.add(unMed);
                }
                Spe uneSpe = new Spe(rs.getString("specialitecomplementaire"), lesMeds);
                lesSpe.add(uneSpe);

            }
            return lesSpe;
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lesSpe;
    }

}
